package com.cn.ccp.service.course;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.bean.CourseContentBean;
import com.cn.ccp.bean.FilePathBean;
import com.cn.ccp.bean.SelectedCourseDetailBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;

public class CourseContentService extends BasicService{
	
	private Log log = LogFactory.getLog(CourseContentService.class);
	
	public int getCourseChapterCount(String courses_id , String chapter_number){
		String sql = "SELECT COUNT(1) FROM courses_detail WHERE courses_id = ? AND chapter_number = ?";
		return jdbcTemplate.queryForInt(sql , new Object[]{courses_id,chapter_number});
	}
	
	public void goDown(final String id,final String type){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					CourseContentBean bean = getCourseContentBean(id);
					if("2".equalsIgnoreCase(type)){//下移动章节下的具体内容
						
						List<CourseContentBean> list =  getZjList(bean.getCourse_id(),bean.getChapter_number(),bean.getChapter_title());
						CourseContentBean b = null;
						for(int i=0, n= list.size(); i<n; i++){
							b = list.get(i);
							if(id.equalsIgnoreCase(String.valueOf(b.getId())) && b.getChapter_title().equalsIgnoreCase(bean.getChapter_title()) && i < n-1){
								String sql = "UPDATE courses_detail SET section_number  = section_number +1 WHERE   id = ? ";
								jdbcTemplate.update(sql,new Object[]{b.getId()});
								sql = "UPDATE courses_detail SET section_number  = section_number - 1 WHERE   id = ? ";
								jdbcTemplate.update(sql,new Object[]{list.get(i+1).getId()});
								break;
							}
						}
						
					}else{//下移动小节下的具体内容
						List<CourseContentBean> list =  getXiaoJList(bean.getCourse_id(),bean.getChapter_number(),bean.getSection_number());
						CourseContentBean b = null;
						for(int i=0, n= list.size(); i<n; i++){
							b = list.get(i);
							if(id.equalsIgnoreCase(String.valueOf(b.getId())) && i < n-1){
								String sql = "UPDATE courses_detail SET other  = other + 1 WHERE   id = ? ";
								jdbcTemplate.update(sql,new Object[]{b.getId()});
								sql = "UPDATE courses_detail SET other  = other - 1 WHERE   id = ? ";
								jdbcTemplate.update(sql,new Object[]{list.get(i+1).getId()});
								break;
							}
						}
					}
					
					
				} catch (Exception e) {
					e.printStackTrace();
				}
				return null;
			}
		});
	}
	
	private List<CourseContentBean> getZjList(String courseId,String chapertId,String chapterTitle){
		String sql = "SELECT * FROM eduonline.courses_detail where courses_id = ? and chapter_number = ? and chapter_title = ?  and section_number > 0 order by section_number+0 ";
		return jdbcTemplate.query(sql, new Object[]{courseId,chapertId,chapterTitle}, new CourseContentRowMapper());
	}
	
	/**
	 * 获取小节下的课程列表
	 * @param courseId
	 * @param chapertId
	 * @return
	 */
	private List<CourseContentBean> getXiaoJList(String courseId,String chapertId,String sctionNUmber){
		String sql = "SELECT * FROM eduonline.courses_detail where courses_id = ? and chapter_number = ? and section_number = ?  order by other+0  asc ";
		return jdbcTemplate.query(sql, new Object[]{courseId,chapertId,sctionNUmber}, new CourseContentRowMapper());
	}
	
	
	public void goUp(final String id , final String type){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					CourseContentBean bean = getCourseContentBean(id);
					if("2".equalsIgnoreCase(type)){//上移动章节下的具体内容
						
						List<CourseContentBean> list =  getZjList(bean.getCourse_id(),bean.getChapter_number(),bean.getChapter_title());
						CourseContentBean b = null;
						for(int i=0, n= list.size(); i<n; i++){
							b = list.get(i);
							if(id.equalsIgnoreCase(String.valueOf(b.getId())) && b.getChapter_title().equalsIgnoreCase(bean.getChapter_title())  && !b.getSection_number().equalsIgnoreCase("1")){
								String sql = "UPDATE courses_detail SET section_number  = section_number -1 WHERE   id = ? ";
								log.info("-----------curid = "+id);
								jdbcTemplate.update(sql,new Object[]{id});
								sql = "UPDATE courses_detail SET section_number  = section_number + 1 WHERE   id = ? ";
								int preid = list.get(i-1).getId();
								log.info("-----------preid = "+preid);
								jdbcTemplate.update(sql,new Object[]{list.get(i-1).getId()});
								break;
							}
						}
						
					}else{//上移动小节下的具体内容
						List<CourseContentBean> list =  getXiaoJList(bean.getCourse_id(),bean.getChapter_number(),bean.getSection_number());
						CourseContentBean b = null;
						for(int i=0, n= list.size(); i<n; i++){
							b = list.get(i);
							if(id.equalsIgnoreCase(String.valueOf(b.getId())) && !b.getOther().equalsIgnoreCase("1")){
								String sql = "UPDATE courses_detail SET other  = other -1 WHERE   id = ? ";
								jdbcTemplate.update(sql,new Object[]{b.getId()});
								sql = "UPDATE courses_detail SET other  = other + 1 WHERE   id = ? ";
								jdbcTemplate.update(sql,new Object[]{list.get(i-1).getId()});
								break;
							}
						}
					}
					
				 
				} catch (Exception e) {
					e.printStackTrace();
				}
				return null;
			}
		});
	}
	
	public CourseContentBean showBeanByTitile(String courseid ,String title){
		String sql = "SELECT * FROM courses_detail WHERE courses_id = ? AND chapter_title = ? AND section_number = 0";
		return jdbcTemplate.queryForObject(sql, new Object[]{courseid ,title} , new CourseContentRowMapper());
	}
	
	
	public boolean sava(CourseContentBean bean){
		
		String sql = "SELECT id FROM file_path WHERE flag = true";
		int filePathId = -1;
		try {
			filePathId = jdbcTemplate.queryForInt(sql);
		} catch (Exception e) {
		}
		
		
		updateCourseStatus(bean.getCourse_id(), "1");
		sql = "INSERT INTO courses_detail "+
					" (courses_id, chapter_number,chapter_title, "+ 
					" section_number,section_file_name,section_file_type, "+ 
					" source_file_url,section_file_url,has_training, "+ 
					" total_score,other,total_pages,`status` ,can_browse ,file_path_id) "+
					" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?);";
		return jdbcTemplate.update(sql,new Object[]{bean.getCourse_id() ,bean.getChapter_number() ,bean.getChapter_title(),
													bean.getSection_number() ,bean.getSection_file_name(),bean.getSection_file_type(),
													bean.getSource_file_url() ,bean.getSection_file_url(),bean.getHas_training(),
													bean.getTotal_score(),bean.getOther() ,bean.getTotal_pages(),bean.getStatus() ,bean.getCan_browse(),filePathId}) > 0;
	}
	
	
	public void updateZhangeJieTitle(String xiaojieId , String newZJTitle ,String newXJTitle){
		
		CourseContentBean bean = showBean(xiaojieId);
		//修改章节标题
		String sql = "UPDATE courses_detail SET chapter_title = ? WHERE courses_id = ? AND chapter_title = ? and chapter_number = ?";
		jdbcTemplate.update(sql , new Object[]{newZJTitle , bean.getCourse_id() , bean.getChapter_title() , bean.getChapter_number() });
		if(!bean.getStatus().equals("0")){
			//修改小节标题
			sql = "UPDATE courses_detail SET chapter_title = ? WHERE courses_id = ? AND chapter_title = ?  and chapter_number = ? ";
			jdbcTemplate.update(sql , new Object[]{newXJTitle , bean.getCourse_id() , bean.getSection_title(),bean.getChapter_number() });
		}
//		String sql = "SELECT c1.* FROM courses_detail c1 "+
//					" LEFT OUTER JOIN courses_detail c2 ON c2.status = c1.chapter_number "+ 
//					" WHERE c2.courses_id = c1.courses_id AND c2.id = "+xiaojieId+" AND c1.section_number = 0 ";
//		CourseContentBean bean = jdbcTemplate.queryForObject(sql, new CourseContentRowMapper() );
//		if(!bean.getChapter_title().equals(newTitle)){
//			sql = "UPDATE courses_detail SET chapter_title = ? WHERE id = ? ";
//			jdbcTemplate.update(sql, new Object[]{newTitle ,bean.getId()});
//		}
	}
	
	public void deleteZJ(String id){
		CourseContentBean ccbean = showBean(id);
		String sql = "select id FROM courses_detail WHERE courses_id = ?  AND chapter_number = ?";
		List<String> list = jdbcTemplate.queryForList(sql,new Object[]{ccbean.getCourse_id() , ccbean.getChapter_number()} ,String.class);
		
		StringBuffer sb = new StringBuffer();
		for(String s : list){
			if(sb.length() == 0)
				sb.append(s);
			else
				sb.append(","+s);
		}
		//删除选课信息
		sql = "DELETE FROM selected_course_detail WHERE course_detail_id IN ("+sb.toString()+");";
		jdbcTemplate.update(sql);
		//删除课程信息
		sql = "DELETE FROM courses_detail WHERE id IN ("+sb.toString()+")";
		jdbcTemplate.update(sql);
		sql = "update courses_detail set chapter_number = chapter_number -1 where courses_id = ? and  chapter_number > ?";
		jdbcTemplate.update(sql , new Object[]{ccbean.getCourse_id(),ccbean.getChapter_number()});;
		
	}
	public void deleteXJ(String id){
		CourseContentBean ccbean = showBean(id);
		log.info(ccbean.getCourse_id()+":"+ccbean.getChapter_number()+":"+ccbean.getChapter_title());
		String sql = "select id FROM courses_detail WHERE courses_id = ?  AND chapter_number = ? AND chapter_title = ?";
		List<String> list = jdbcTemplate.queryForList(sql,new Object[]{ccbean.getCourse_id() , ccbean.getChapter_number() , ccbean.getSection_title()} ,String.class);
		
		StringBuffer sb = new StringBuffer();
		for(String s : list){
			if(sb.length() == 0)
				sb.append(s);
			else
				sb.append(","+s);
		}
		log.info(sb.toString());
//		//删除选课信息
		sql = "DELETE FROM selected_course_detail WHERE course_detail_id IN ("+sb.toString()+");";
//		log.info(sql);
		jdbcTemplate.update(sql);
		//删除课程信息
		sql = "DELETE FROM courses_detail WHERE id IN ("+sb.toString()+")";
		log.info(sql);
		jdbcTemplate.update(sql);
		
		sql = "update courses_detail set section_number = section_number -1 where courses_id = ? and chapter_number = ? and section_number > ?";
		jdbcTemplate.update(sql , new Object[]{ccbean.getCourse_id(),ccbean.getChapter_number(),ccbean.getSection_number()});;
	}
	
	
	
	public boolean update(CourseContentBean bean){
		String sql = "SELECT id FROM file_path WHERE flag = true";
		int filePathId = -1;
		try {
			filePathId = jdbcTemplate.queryForInt(sql);
		} catch (Exception e) {
		}
		
		CourseContentBean oldBean = showBean(""+bean.getId());
		if(!oldBean.getSection_file_url().equalsIgnoreCase(bean.getSection_file_url())){
			
			File sourseFile = null;
			if(oldBean.getFilePathId() !=  -1){
				sql = "SELECT * FROM file_path WHERE id = ?";
				FilePathBean b = (FilePathBean) jdbcTemplate.queryForObject(sql, new Object[] { oldBean.getFilePathId() }, new FilePathRowMapper());
				sourseFile = new File(b.getPath()+oldBean.getSection_file_url());
			}else
				sourseFile = new File(CCPContants.SYS_PATH+"files"+File.separator + oldBean.getSection_file_url());
			if(sourseFile.exists()) 
				sourseFile.delete();
//			File file = new File(CCPContants.SYS_PATH+File.separator+"files"+File.separator+CCPContants.getCurrUserId()+File.separator+oldBean.getSource_file_url());
//			if(file.exists()) file.delete();
//			
//			file = new File(CCPContants.SYS_PATH+File.separator+"files"+File.separator+CCPContants.getCurrUserId()+File.separator+oldBean.getSection_file_url());
//			if(file.exists()) file.delete();
		}
		
		
		updateCourseStatus(bean.getCourse_id(), "1");
		sql = "update  courses_detail "+
		" set courses_id = ?, chapter_number= ?,chapter_title= ?, "+ 
		" section_number= ?,section_file_name= ?,section_file_type= ?, "+ 
		" source_file_url= ?,section_file_url= ?,has_training= ?, "+ 
		" total_score= ?,other= ?,total_pages= ? ,can_browse = ? ,file_path_id = ? ,`status` = ? where id = ? ";
		log.info(bean);
		return jdbcTemplate.update(sql,new Object[]{bean.getCourse_id() ,bean.getChapter_number() ,bean.getChapter_title(),
										bean.getSection_number() ,bean.getSection_file_name(),bean.getSection_file_type(),
										bean.getSource_file_url() ,bean.getSection_file_url(),bean.getHas_training(),
										bean.getTotal_score() ,bean.getOther() ,bean.getTotal_pages(), bean.getCan_browse() ,filePathId,bean.getStatus() ,bean.getId()}) > 0;
	}
	
	public void updateCourseStatus(String id , String status){
		String sql = "update courses set `status` = ? where id = ?";
		jdbcTemplate.update(sql, new Object[]{status ,id});
	}
	
	/**
	 * 删除叶子节点
	 * @param id
	 */
	public void deleteCourseDetailYezhi(String id){
		String sql = "SELECT cd.*  FROM courses_detail  cd  WHERE cd.id = ?   ";
		CourseContentBean bean = (CourseContentBean) jdbcTemplate.queryForObject(sql, new Object[]{id},new CourseContentRowMapper());
	}
	
	
	
	public boolean delete(String id,int type){
		boolean flag = false ;
		CourseContentBean ccb = showBean(id);
		String sql = "delete from courses_detail where id = ?";
		flag = jdbcTemplate.update(sql , new Object[]{id}) > 0;
		updateCourseStatus(ccb.getCourse_id(), "1");
		
		File sourseFile = null;
		if(ccb.getFilePathId() !=  -1){
			sql = "SELECT * FROM file_path WHERE id = ?";
			FilePathBean b = (FilePathBean) jdbcTemplate.queryForObject(sql, new Object[] { ccb.getFilePathId() }, new FilePathRowMapper());
			sourseFile = new File(b.getPath()+ccb.getSection_file_url());
		}else
			sourseFile = new File(CCPContants.SYS_PATH+"files"+File.separator + ccb.getSection_file_url());
		if(sourseFile.exists()) 
			sourseFile.delete();
		switch(type){
			case 1: //删除章节
				log.info("------delete zhangjie  ----");
				sql = "update courses_detail set chapter_number = chapter_number -1 where courses_id = ? and  chapter_number > ?";
				jdbcTemplate.update(sql , new Object[]{ccb.getCourse_id(),ccb.getChapter_number()});;
				break;
			case 2://删除章节下的具体内容
			case 3://删除小节标题
				log.info("------delete zhangjie contents  or xiaojiao ----");
				sql = "update courses_detail set section_number = section_number -1 where courses_id = ? and chapter_number = ? and section_number > ?";
				jdbcTemplate.update(sql , new Object[]{ccb.getCourse_id(),ccb.getChapter_number(),ccb.getSection_number()});;
				break;
			case 4://删除小节下的具体内容
				log.info("------delete xiaojiao contents----");
				sql = "update courses_detail set other = other -1 where courses_id = ? and chapter_number = ? and section_number = ? and other > ? ";
				jdbcTemplate.update(sql , new Object[]{ccb.getCourse_id(),ccb.getChapter_number(),ccb.getSection_number(),ccb.getOther()});
				break;	
		} 
		
		
		
		return flag ;
	}
	
	
	public List<CourseContentBean> getCourseContentList(String course_id){
		
		return getCourseContentList(course_id, "");
	}
	
	public List<CourseContentBean> getCourseContentList(String course_id ,String selectCourseType){
		String sql = "";
		Object[] objs = null ;
		if(selectCourseType == null || selectCourseType.equals("")){
			sql = "SELECT cd.* ,c.courses_name FROM courses_detail cd "+
			" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
			" WHERE cd.courses_id = ?   ORDER BY  cd.chapter_number+0  ,cd.section_number+0,cd.other+0 asc";
			objs = new Object[]{course_id};
		}else{
			sql = "SELECT cd.* ,c.courses_name FROM courses_detail cd "+
			" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
			" LEFT OUTER JOIN selected_course_detail scd ON scd.course_detail_id = cd.id "+
			" WHERE cd.courses_id = ?  and cd .section_file_type = ? ORDER BY  cd.chapter_number+0  ,cd.section_number +0,cd.other+0  asc";
			objs = new Object[]{course_id , selectCourseType};
		}
		return jdbcTemplate.query(sql, objs, new CourseContentRowMapper());
	}
	
	public List<String> getCourseTypleList(String courseId){
		String sql = "SELECT section_file_type FROM courses_detail WHERE courses_id = ? AND section_file_type IS NOT NULL GROUP  BY  section_file_type ";
		return jdbcTemplate.queryForList(sql, new String[]{courseId},String.class );
	}
	
	
	
	/**
	 * 根据课程id 查询该门课程的所有课程内容
	 * @param courseId 课程id
	 * @param selectedCourseId 选课id
	 * @return
	 */
	public List<SelectedCourseDetailBean> getOnlineCourseContentList(String courseId,String selectedCourseId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT cd.* ,c.courses_name,scd.has_studied FROM courses_detail cd ");
		sql.append(" LEFT OUTER JOIN courses c ON c.id = cd.courses_id ");
		sql.append(" left outer join selected_course_detail scd on scd.course_detail_id = cd.id  and scd.selected_course_id = ?"); 
		sql.append(" WHERE cd.courses_id = ?   ");
		sql.append(" ORDER BY cd.chapter_number,cd.section_number+0 asc ");
		return jdbcTemplate.query(sql.toString(), new Object[]{selectedCourseId,courseId}, new SelectedCourseDetailMapper());
	}
	
	
	/**
	 * 查询需要考试/练习的课程章节
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<CourseContentBean> getTestChapterLis( int courseId ){
		String sql = "SELECT cd.* ,c.courses_name ,SUM(t.training_score) AS training_total_score "+
					" FROM courses_detail  cd  "+
					" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+ 
					" LEFT OUTER JOIN training t ON t.chapter_course_id = cd.id AND t.is_chapter = 1 "+
					" WHERE cd.has_training = 1 AND cd.courses_id = ?  "+
					" GROUP BY cd.id ";
		return jdbcTemplate.query(sql, new Object[]{courseId }, new CourseContentRowMapper());
	}
	
	public List<CourseContentBean> getCourseContentChapterList(String courses_id ,String chapter_number){
		String sql = "SELECT cd.* ,c.courses_name FROM courses_detail  cd "+
						" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
						" WHERE cd.courses_id =? and cd.chapter_number = ?   ";
		return jdbcTemplate.query(sql, new Object[]{courses_id ,chapter_number}, new CourseContentRowMapper());
	}
	
	public List<CourseContentBean> getCourseTitleList(String course_id){
		String sql = "SELECT cd.* ,c.courses_name FROM courses_detail  cd "+
					" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
					" WHERE cd.courses_id = ?   GROUP BY cd.chapter_title ORDER BY cd.chapter_number DESC ";
		return jdbcTemplate.query(sql, new Object[]{course_id}, new CourseContentRowMapper());
	}
	
	public List<CourseContentBean> getCourseList(){
		String sql = "SELECT cd.* ,c.courses_name FROM courses_detail  cd "+
					" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
					" WHERE cd.courses_id = ?   ";
		return jdbcTemplate.query(sql, new CourseContentRowMapper());
	}
	
	 public List<CourseContentBean> getCourseContentsList(String courseId){
		 StringBuffer sql = new StringBuffer();
		 sql.append("SELECT cd.* ,c.courses_name FROM courses_detail cd ");
		 sql.append("LEFT JOIN courses c ON c.id = cd.courses_id  ");
		 sql.append("WHERE cd.courses_id = ? ");
		 sql.append("ORDER BY cd.id,cd.chapter_number,cd.section_number ASC  ");
		 return jdbcTemplate.query(sql.toString(), new Object[]{courseId},  new CourseContentRowMapper());
	 }
	
	
	
	public List<CourseContentBean> getZhangeJieCourseList(String courseId){
		String sql = "SELECT cd.* ,c.courses_name FROM courses_detail cd  "+
					" LEFT JOIN courses c ON c.id = cd.courses_id "+
					" WHERE cd.courses_id = ? AND cd.section_number = '0' ORDER BY cd.chapter_number ASC  ";
		return jdbcTemplate.query(sql, new Object[]{courseId},  new CourseContentRowMapper());
	}
	public List<CourseContentBean> getXiaoJieCourseList(String courseId, String chapterNumber ,boolean isZhangejie){
		String sql = "";
		if(isZhangejie)
			sql = "SELECT cd.*,c.courses_name,scd.id as selectCourseId,scd.has_studied FROM courses_detail cd "+
			" LEFT JOIN courses c ON c.id = cd.courses_id "+
			" LEFT JOIN selected_course_detail scd ON scd.course_detail_id = cd.id "+
			" WHERE cd.courses_id = ? and cd.chapter_number = ? and cd.section_number != 0 and cd.status = 0   ORDER BY cd.status ASC ,cd.section_number ";
		else
			sql = "SELECT cd.*,c.courses_name,scd.id as selectCourseId,scd.has_studied FROM courses_detail cd "+
						" LEFT JOIN courses c ON c.id = cd.courses_id "+
						" LEFT JOIN selected_course_detail scd ON scd.course_detail_id = cd.id "+
						" WHERE cd.courses_id = ? and cd.status = ? ORDER BY cd.status ASC ,cd.chapter_title ,cd.section_number  ";
		return jdbcTemplate.query(sql, new Object[]{courseId,chapterNumber}, new CourseContentRowMapper());
	}
	
	
	public List<CourseContentBean> getChapterDetailList(String courseId,String chapterNum){
		String sql = "SELECT cd.* FROM courses_detail  cd "+
						" WHERE cd.courses_id = ? AND cd.chapter_number = ?   ";
		return jdbcTemplate.query(sql, new Object[]{courseId,chapterNum},new CourseContentRowMapper());
	}
	
	public boolean checkHaveZJ( String courseId ,String sectionNumber) {
		String sql = "SELECT count(1) FROM courses_detail WHERE courses_id= ? AND section_number = 0 AND chapter_number = ?";
		return jdbcTemplate.queryForInt(sql , new Object[]{courseId,sectionNumber})>0;
	}
	
	
	public List<String>  getXiaojieDetailList(CourseContentBean bean){
		String sql = "SELECT DISTINCT chapter_title FROM courses_detail WHERE courses_id = ?  AND chapter_number = ? AND `status` = ? ";
		return jdbcTemplate.queryForList(sql, new Object[]{bean.getCourse_id() ,bean.getChapter_number() ,bean.getSection_index()}, String.class);
	}
	
	public List<String>  getXiaojieTitleList(CourseContentBean bean){
		String sql = "SELECT DISTINCT chapter_title FROM courses_detail WHERE courses_id = ?  AND chapter_number = ? order by section_number asc";
		return jdbcTemplate.queryForList(sql, new Object[]{bean.getCourse_id() ,bean.getChapter_number() }, String.class);
	}
	
	
	public void changeSectionFileName(String contentsName,int id){
		String sql = "update courses_detail set section_file_name =  ? where id = ? ";
		jdbcTemplate.update(sql, new Object[]{contentsName,id});
	}
	
	
	public void changeChapterTitleName(String chapterName,int id){
		CourseContentBean b = showBean(String.valueOf(id));
		String sql = "update courses_detail set chapter_title =  ? where id = ? and chapter_title = ?";
		jdbcTemplate.update(sql, new Object[]{chapterName,id,b.getChapter_title()});
	}
	
	public CourseContentBean getCourseContentBean(String courseContentId){
		String sql = "SELECT cd.* ,c.courses_name FROM courses_detail  cd "+
					" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
					" WHERE cd.id = ?   ";
		CourseContentBean bean = (CourseContentBean) jdbcTemplate.queryForObject(sql, new Object[]{courseContentId},new CourseContentRowMapper());
		return bean ;
	}
	
	
	
	
	public CourseContentBean showBean(String courseContentId){
		String sql = "SELECT cd.* ,c.courses_name FROM courses_detail  cd "+
					" LEFT OUTER JOIN courses c ON c.id = cd.courses_id "+
					" WHERE cd.id = ?   ";
		CourseContentBean bean = (CourseContentBean) jdbcTemplate.queryForObject(sql, new Object[]{courseContentId},new CourseContentRowMapper());
		if(bean != null){
			if(!bean.getStatus().equals("0")){
				sql = "SELECT * FROM courses_detail WHERE chapter_number = ? AND courses_id = ? AND section_number = 0 ";
				CourseContentBean cbean = jdbcTemplate.queryForObject(sql, new Object[]{bean.getStatus() ,bean.getCourse_id()}, new CourseContentRowMapper());
				
				
				
				bean.setSection_title(bean.getChapter_title());
				bean.setChapter_title(cbean.getChapter_title());
			}
		}
		return bean ;
	}
	
	public int getCurrNumber(CourseContentBean bean){
		String sql = "SELECT  COUNT( distinct section_number ) FROM courses_detail WHERE courses_id = ?  AND chapter_number = ? AND section_number != 0 ";
		int number = 0;
		try{
			number = jdbcTemplate.queryForInt(sql , new Object[]{bean.getCourse_id() ,bean.getChapter_number() });
		}catch(Exception e){
			log.error("get section number failed : ", e);
		}
		return number;
	}
	
	/**
	 * 获取小节信息section number 
	 * @param bean
	 * @return
	 */
	public List getXiaoJieSectionNumber(CourseContentBean bean){
		String sql = "SELECT section_number  FROM courses_detail WHERE courses_id = ?  AND chapter_number = ? and chapter_title = ? AND  section_number != 0 ";
		List<String> list = null;
		try{
			list =  jdbcTemplate.queryForList(sql , new Object[]{bean.getCourse_id() ,bean.getChapter_number() , bean.getChapter_title()},String.class);
		}catch(Exception e){
			log.error("query section nubmer failed : ", e);
		}
		return list;
	}
	
	
	
	public boolean checkSectionNumber(int id ,String courseid ,String chapter_number ,String section_number){
		String sql = "";
		Object[] objs = null ;
		if(id != 0){
			sql = "SELECT COUNT(1) FROM courses_detail WHERE chapter_number = ? AND section_number = ? and courses_id = ? and id != ?";
			 objs = new Object[]{chapter_number,section_number ,courseid ,id} ;
		}else{
			sql = "SELECT COUNT(1) FROM courses_detail WHERE chapter_number = ? AND section_number = ? and courses_id = ?";
			objs = new Object[]{chapter_number,section_number ,courseid} ;
		}
		
		return jdbcTemplate.queryForInt(sql ,objs) > 0;
	}
	
	 
	public class CourseContentRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseContentBean bean = new CourseContentBean();
			bean.setId(rs.getInt("id"));
			bean.setTmpId(bean.getId());
			bean.setCourse_id(rs.getString("courses_id"));
//			CourseBean cbean = BaseAction.getInstance().getCourseService().getBeanById(bean.getCourse_id());
			bean.setCourse_name("");
			try {
				bean.setCourse_name(rs.getString("courses_name"));
			} catch (Exception e) {
			}
			bean.setChapter_number(rs.getString("chapter_number"));
			bean.setChapter_title(rs.getString("chapter_title"));
			bean.setSection_number(""+rs.getString("section_number"));
			bean.setSection_file_name(rs.getString("section_file_name"));
			bean.setSection_file_type(rs.getString("section_file_type"));
			bean.setSource_file_url(rs.getString("source_file_url"));
			bean.setSection_file_url(rs.getString("section_file_url"));
			bean.setHas_training(rs.getString("has_training"));
			bean.setOther(rs.getString("other"));
			bean.setTotal_pages(rs.getString("total_pages"));
			bean.setCan_browse(rs.getString("can_browse"));
			bean.setTotal_score(rs.getInt("total_score"));
			bean.setUpdateState(true);
			try {
				bean.setTraining_total_score(rs.getInt("training_total_score"));
			} catch (Exception e) {
				bean.setTraining_total_score(0);
			}
			bean.setFilePathId(rs.getInt("file_path_id")); 
			bean.setStatus(rs.getString("status"));
			
			bean.setSection_index(bean.getStatus());
			
			
			try {
				bean.setSelectCourseId(rs.getString("selectCourseId"));
				bean.setHaveStudty(rs.getBoolean("has_studied"));	
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			return bean;
		}
	}
	
	public class SelectedCourseDetailMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			SelectedCourseDetailBean bean = new SelectedCourseDetailBean();
//			bean.setId(rs.getInt("id"));
//			bean.setSelectedCourseId(rs.getInt("selected_course_id"));
			bean.setCourseDetailId(rs.getInt("id"));
			bean.setChapterNumber(rs.getInt("chapter_number"));
			bean.setChapterTitle(rs.getString("chapter_title"));
			bean.setSectionNumber(rs.getString("section_number"));
			bean.setSectionFileName(rs.getString("section_file_name"));
			bean.setSectionFileType(rs.getString("section_file_type"));
			bean.setHasStudied(rs.getBoolean("has_studied"));
//			bean.setHasTrained(rs.getBoolean("has_trained"));
			bean.setFileUrl(rs.getString("section_file_url"));
//			bean.setHasTraining(rs.getBoolean("has_training"));
			return bean;
		}
	}
	
	class FilePathRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			FilePathBean bean = new FilePathBean();
			bean.setId(rs.getInt("id"));
			bean.setPath(rs.getString("path"));
			bean.setFlag(rs.getBoolean("flag"));
			if(bean.isFlag())
				bean.setPathStatus("正常");
			else
				bean.setPathStatus("禁用");
			return bean;
		}
	}
}
